﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using MySql.Data.MySqlClient;

/// <summary>
/// StaffDB 的摘要说明
/// This calls should be a singleton class, because we have no need to create a new connection if there are server DB calls in a request
/// </summary>
public class StaffDB : CommonDb
{
    private static StaffDB m_StaffDb = null;

    public static StaffDB Singleton()
    {
        if(m_StaffDb == null)
        {
            m_StaffDb = new StaffDB();
        }
        return m_StaffDb;
    }

    public static string[] PositionNames = { "正职", "副职", "普通"};

    public StaffDB()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
       
      
	}

    public DataSet GetStaffSummary()
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetStaffSummary", m_conn);
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        try
        {
            m_conn.Open();
            da.Fill(ds, "staffSum");
            return ds;
        }
        catch(MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }

    public StaffDetail GetStaffBySNum(int SerailNum)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetStaffBySNum", m_conn);
        cmd.CommandType = CommandType.StoredProcedure;

        DataComponent.AddCmdParameters(cmd, "?staffSerialNum", MySqlDbType.Int32, 10, SerailNum);
        StaffDetail staff = new StaffDetail();

        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                staff.Id = (int)reader["Id"];
                staff.Name = (string)reader["Name"];
                staff.DepartmentNum = (int)reader["DepartmentNum"];
                staff.SelfScore = (int)reader["SelfScore"];
                staff.SerialNumber = (int)reader["SerialNum"];
            }
            reader.Close();
            return staff;
        }
        catch(MySqlException err)
        {
            throw new ApplicationException("Data Error");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public void UpdateLeaderScoreBySN(StaffDetail staffInfo)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateLeaderScoreBySN", m_conn);
        DataComponent.AddCmdParameters(cmd, "?staffLeaderScore", MySqlDbType.Int32, 10, staffInfo.ScoreByLeader);
        DataComponent.AddCmdParameters(cmd, "?staffSerialNum", MySqlDbType.Int32, 10, staffInfo.SerialNumber);

        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }


    public void UpdateSelfScoreBySN(StaffDetail staffInfo)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateSelfScoreBySN", m_conn);
        DataComponent.AddCmdParameters(cmd, "?staffSelfScore", MySqlDbType.Int32, 10, staffInfo.SelfScore);
        DataComponent.AddCmdParameters(cmd, "?staffSerialNum", MySqlDbType.Int32, 10, staffInfo.SerialNumber);

        try
        {
            m_conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch(MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }

    public void UpdateStaffScore(StaffDetail staffInfo) 
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateStaffScore", m_conn);
        DataComponent.AddCmdParameters(cmd, "?staffSerialNum", MySqlDbType.Int32, 10 , staffInfo.SerialNumber);
        DataComponent.AddCmdParameters(cmd, "?staffLeaderScore", MySqlDbType.Int32, 10, staffInfo.LeaderScore);
        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }

    public void UpdateLeaders(int Id, int ScoreBySegment, int LeaderScore)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateLeaders", m_conn);
        DataComponent.AddCmdParameters(cmd, "?dptId", MySqlDbType.Int32, 10, Id);
        DataComponent.AddCmdParameters(cmd, "?dptScoreBySegment", MySqlDbType.Int32,10,ScoreBySegment);
        DataComponent.AddCmdParameters(cmd, "?staffScoreByLeader", MySqlDbType.Int32, 10, LeaderScore);

        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }

    public void UpdateStaffPwd(string UserName, string Password)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateStaffPwd", m_conn);
        DataComponent.AddCmdParameters(cmd, "?staffName", MySqlDbType.VarChar, 45, int.Parse(UserName));
        DataComponent.AddCmdParameters(cmd, "?staffPwd", MySqlDbType.VarChar, 45, Password);
        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }

    public void UpdateAdminPwd(string UserName, string Password)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateAdminPwd", m_conn);
        DataComponent.AddCmdParameters(cmd, "?adminName", MySqlDbType.VarChar, 45, int.Parse(UserName));
        DataComponent.AddCmdParameters(cmd, "?adminPwd", MySqlDbType.VarChar, 45, Password);
        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }

    public DataSet GetLeaders()
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetLeaders", m_conn);
        DataSet ds = new DataSet();
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        try
        {
            m_conn.Open();
            da.Fill(ds, "Leaders");
            return ds;
        }
        catch (MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }

    public DataSet GetViceLeaders(int DirectorSN)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetViceLeaders", m_conn);
        DataComponent.AddCmdParameters(cmd, "dsn", MySqlDbType.Int32, 10, DirectorSN);
        DataSet ds = new DataSet();
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        try
        {
            m_conn.Open();
            da.Fill(ds, "viceLeaders");
            return ds;
        }
        catch(MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();   
        }
    }

    public StaffDetail[] GetStaffsByVerifier(int Verifier)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetStaffsByVerifier", m_conn);
        DataComponent.AddCmdParameters(cmd, "?staffVerifier", MySqlDbType.Int32, 10, Verifier);
        // Create a collection for all the employee records.
        ArrayList staffs = new ArrayList();

        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                StaffDetail staff = new StaffDetail();
                staff.Id = (int)reader["Id"];
                staff.Name = (string)reader["Name"];
                staff.DepartmentNum = (int)reader["DepartmentNum"];
                staff.ScoreByLeader = (int)reader["LeaderScore"];
                staff.SelfScore = (int)reader["SelfScore"];
                staff.SerialNumber = (int)reader["SerialNum"];
                staff.Level = (int)reader["Level"];
                staff.Withdraw = (int)reader["WithDraw"];
                staff.Position = (int)reader["Position"];
                if (reader["Title"].GetType() == typeof(DBNull))
                {
                    staff.Title = "";
                }
                else
                {
                    staff.Title = (string)reader["Title"];
                }


                staff.Evaluator = (int)reader["Evaluator"];
                staff.Verifier = (int)reader["Verifier"];
                if (reader["WdReason"].GetType() == typeof(DBNull))
                {
                    staff.WdReason = "-";
                }
                else
                {
                    staff.WdReason = (string)reader["WdReason"];
                }


                staffs.Add(staff);
            }

            reader.Close();
            //Add a null row for grid display, because the gridview 
            // won't display when there is no data.
            if (staffs.Count == 0)
            {
                StaffDetail staff = new StaffDetail();
                staff.Id = 0;
                staff.Name = "-";
                staff.Withdraw = 0;
                staff.WdReason = "-";
                staffs.Add(staff);
            }
            return (StaffDetail[])staffs.ToArray(typeof(StaffDetail));
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public StaffDetail[] GetStaffsByEvaluator(int Evaluator)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("GetStaffsByEvaluator", m_conn);
        DataComponent.AddCmdParameters(cmd, "?staffEvaluator", MySqlDbType.Int32, 10, Evaluator);
        // Create a collection for all the employee records.
        ArrayList staffs = new ArrayList();

        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                StaffDetail staff = new StaffDetail();
                staff.Id = (int)reader["Id"];
                staff.Name = (string)reader["Name"];
                staff.DepartmentNum = (int)reader["DepartmentNum"];
                staff.ScoreByLeader = (int)reader["LeaderScore"];
                staff.SelfScore = (int)reader["SelfScore"];
                staff.SerialNumber = (int)reader["SerialNum"];
                staff.Level = (int)reader["Level"];
                staff.Withdraw = (int)reader["WithDraw"];
                staff.Position = (int)reader["Position"];
                if (reader["Title"].GetType() == typeof(DBNull))
                {
                    staff.Title = "";
                }
                else
                {
                    staff.Title = (string)reader["Title"];
                }


                staff.Evaluator = (int)reader["Evaluator"];
                staff.Verifier = (int)reader["Verifier"];
                if (reader["WdReason"].GetType() == typeof(DBNull))
                {
                    staff.WdReason = "-";
                }
                else
                {
                    staff.WdReason = (string)reader["WdReason"];
                }


                staffs.Add(staff);
            }

            reader.Close();
            //Add a null row for grid display, because the gridview 
            // won't display when there is no data.
            if (staffs.Count == 0)
            {
                StaffDetail staff = new StaffDetail();
                staff.Id = 0;
                staff.Name = "-";
                staff.Withdraw = 0;
                staff.WdReason = "-";
                staffs.Add(staff);
            }
            return (StaffDetail[])staffs.ToArray(typeof(StaffDetail));
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public StaffDetail[] GetStaffs(int DepartmentNum)
    {
        MySqlCommand cmd = null;
        if( DepartmentNum != 0)
        {
            cmd = DataComponent.CreateMySqlProcCmd("GetStaffInfoByDepartment", m_conn);
            DataComponent.AddCmdParameters(cmd, "?DptNum", MySqlDbType.Int32, 10, DepartmentNum);
        }
        else //if DepartmentNum = 0, we get all staffs
        {
            cmd = DataComponent.CreateMySqlProcCmd("GetStaffInfo", m_conn);
        }

        // Create a collection for all the employee records.
        ArrayList staffs = new ArrayList();

        try
        {
            m_conn.Open();
            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                StaffDetail staff = new StaffDetail();
                staff.Id = (int)reader["Id"];
                staff.Name = (string)reader["Name"];
                staff.DepartmentNum = (int)reader["DepartmentNum"];
                staff.ScoreByLeader = (int)reader["LeaderScore"];
                staff.SelfScore = (int)reader["SelfScore"];
                staff.SerialNumber = (int)reader["SerialNum"];
                staff.Level = (int)reader["Level"];
                staff.Withdraw = (int)reader["WithDraw"];
                staff.Position = (int)reader["Position"];
                if (reader["Title"].GetType() == typeof(DBNull))
                {
                    staff.Title = "";
                }
                else
                {
                    staff.Title = (string)reader["Title"];
                }
               
                
                staff.Evaluator = (int)reader["Evaluator"];
                staff.Verifier = (int)reader["Verifier"];
                if (reader["WdReason"].GetType() == typeof(DBNull))
                {
                    staff.WdReason = "-";
                }
                else
                {
                    staff.WdReason = (string)reader["WdReason"];
                }


                staffs.Add(staff);
            }

            reader.Close();
            //Add a null row for grid display, because the gridview 
            // won't display when there is no data.
            if (staffs.Count == 0)
            {
                StaffDetail staff = new StaffDetail();
                staff.Id = 0;
                staff.Name = "-";
                staff.Withdraw = 0;
                staff.WdReason = "-";
                staffs.Add(staff);
            }
            return (StaffDetail[])staffs.ToArray(typeof(StaffDetail));
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public void UpdateWithdraw(StaffDetail staffInfo)
    {
        MySqlCommand cmd = DataComponent.CreateMySqlProcCmd("UpdateWithdraw", m_conn);
        DataComponent.AddCmdParameters(cmd, "?staffSerialNum", MySqlDbType.Int32, 10, staffInfo.SerialNumber);
        DataComponent.AddCmdParameters(cmd, "?staffWithdraw", MySqlDbType.Int32, 10, staffInfo.Withdraw);
        DataComponent.AddCmdParameters(cmd, "?staffWdReason", MySqlDbType.Text, 100, staffInfo.WdReason);
        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }

    public void UpdateStaffInfo(StaffDetail staffInfo)
    {
        MySqlCommand cmd = new MySqlCommand("UpdateStaffInfo", m_conn);
        cmd.CommandType = CommandType.StoredProcedure;
      
        cmd.Parameters.Add(new MySqlParameter("?staffId", MySqlDbType.Int32, 10));
        cmd.Parameters["?staffId"].Value = staffInfo.Id;

        cmd.Parameters.Add(new MySqlParameter("?staffSerialNum", MySqlDbType.Int32, 10));
        cmd.Parameters["?staffSerialNum"].Value = staffInfo.SerialNumber;

        cmd.Parameters.Add(new MySqlParameter("?staffName", MySqlDbType.VarChar, 45));
        cmd.Parameters["?staffName"].Value = staffInfo.Name;
        
        cmd.Parameters.Add(new MySqlParameter("?staffDepartmentNum", MySqlDbType.Int32, 10));
        cmd.Parameters["?staffDepartmentNum"].Value = staffInfo.DepartmentNum;
     
        DataComponent.AddCmdParameters(cmd, "?staffLevel", MySqlDbType.Int32, 10, staffInfo.Level);

        DataComponent.AddCmdParameters(cmd, "?staffPosition", MySqlDbType.Int32, 10, staffInfo.Position);

        DataComponent.AddCmdParameters(cmd, "?staffTitle", MySqlDbType.VarChar, 45, staffInfo.Title);

        DataComponent.AddCmdParameters(cmd, "?staffEvaluator", MySqlDbType.VarChar, 45, staffInfo.Evaluator);

        DataComponent.AddCmdParameters(cmd, "?staffVerifier", MySqlDbType.VarChar, 45, staffInfo.Verifier);
        
        DataComponent.ExecNonQueryCmd(cmd, m_conn);
    }
    public bool IsSerialNumberExist(int serialNum)
    {
        string cmdText = "select Count(*) from staff where SerialNum = " + serialNum.ToString();
        MySqlCommand cmd = new MySqlCommand(cmdText, m_conn);
        try
        {
            m_conn.Open();
            MySqlDataReader reader =  cmd.ExecuteReader();
            long count = 0;
            if(reader.Read())
            {
                count = (long)reader["Count(*)"];
            }
            return count>0 ? true:false;
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }
    public int InsertStaff(StaffDetail staff)
    {        
        MySqlCommand cmd = new MySqlCommand("InsertStaff", m_conn);
        cmd.CommandType = CommandType.StoredProcedure;

        //set proc parameters
        DataComponent.AddCmdParameters(cmd, "?staffSerialNum", MySqlDbType.Int32, 10, staff.SerialNumber);
        DataComponent.AddCmdParameters(cmd, "?staffName", MySqlDbType.VarChar, 45,staff.Name);

        DataComponent.AddCmdParameters(cmd, "?staffDepartmentNum", MySqlDbType.Int32, 10, staff.DepartmentNum);

        DataComponent.AddCmdParameters(cmd, "?staffLevel", MySqlDbType.Int32, 10, staff.Level);

        DataComponent.AddCmdParameters(cmd, "?staffTitle", MySqlDbType.VarChar, 45, staff.Title);

        DataComponent.AddCmdOutParameters(cmd, "?staffId", MySqlDbType.Int32, 10);
     
        try
        {
            m_conn.Open();
            cmd.ExecuteNonQuery();
            return (int)cmd.Parameters["?staffId"].Value;
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public void DeleteStaff(StaffDetail staff)
    {
        MySqlCommand cmd = new MySqlCommand("DeleteStaffById", m_conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new MySqlParameter("?StaffId", MySqlDbType.Int32, 10));
        cmd.Parameters["?StaffId"].Value = staff.Id;

        try
        {
            m_conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException err)
        {
            // Replace the error with something less specific.
            // You could also log the error now.
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }
    }

    public int GetSegmentManagerSN()
    {
        string SqlCmd = "select ManagerSN from segment";
        MySqlCommand cmd = new MySqlCommand(SqlCmd, m_conn);
        int  ManagerSN = 0;
        try
        {
            MySqlDataReader reader = cmd.ExecuteReader();
            if(reader.Read())
            {
                ManagerSN = (int)reader["ManagerSN"];
            }
            return ManagerSN;
        }
        catch (MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
        finally
        {
            m_conn.Close();
        }
    }

    private bool IsViceLeader(string UserName, int DptNum)
    {
        string sql = string.Format("select * from staff where DepartmentNum={0} and Evaluator={1}", DptNum.ToString(), UserName);
        MySqlCommand cmd = new MySqlCommand(sql, m_conn);
        try
        {
            MySqlDataReader reader = cmd.ExecuteReader();
            int count = 0;
            while(reader.Read())
            {
                count++;
            }
            reader.Close();
            return count > 0 ? true : false;
        }
         catch (MySqlException err)
        {
            throw new ApplicationException(err.ToString());
        }
      

    }

    public int GetUserGroup(string UserName, string Password)
    {
        MySqlCommand cmd = new MySqlCommand("GetStaffDpt", m_conn);
        cmd.CommandType = CommandType.StoredProcedure;
        UserGroup userGroup = UserGroup.Guest;   //Guest

        DataComponent.AddCmdParameters(cmd, "?staffPwd", MySqlDbType.VarChar, 45, Password);
        if (UserName == "")
        {
            UserName = "0";
        }
        DataComponent.AddCmdParameters(cmd, "?staffSerialNum", MySqlDbType.Int32, 10, int.Parse(UserName));
        DataComponent.AddCmdOutParameters(cmd, "?DptNum", MySqlDbType.Int32, 10);
        DataComponent.AddCmdOutParameters(cmd, "?staffName", MySqlDbType.VarChar, 45);
        try
        {
            m_conn.Open();
            cmd.ExecuteNonQuery();
            if(cmd.Parameters["?DptNum"].Value.GetType() != typeof(DBNull))
            {
                //The staff exists, we will check if he is a leader
                userGroup = UserGroup.Staff;    //staff
                int dptNum = (int)cmd.Parameters["?DptNum"].Value;
                string Name = (string)cmd.Parameters["?staffName"].Value;
                //Check if he is a Vice lead
                if (IsViceLeader(UserName, dptNum))
                {
                    userGroup = UserGroup.ViceLead;
                }
                
               
                cmd.Parameters.Clear();
                cmd.CommandText = "GetDptLeader";
                cmd.CommandType = CommandType.StoredProcedure;
                DataComponent.AddCmdParameters(cmd,"?DptNum", MySqlDbType.Int32, 10, dptNum);
                DataComponent.AddCmdOutParameters(cmd, "?dptLeaderName", MySqlDbType.VarChar,45);
                
                cmd.ExecuteNonQuery();
                string LeaderName = (string)cmd.Parameters["?dptLeaderName"].Value;
                if(String.Compare(Name,LeaderName,true) == 0)
                {
                    userGroup = UserGroup.Lead;  //Leader
                }

                //Check if he is a director
                DepartmentDb dptDb = new DepartmentDb();
                int[] Directors = dptDb.GetDirectors();
                foreach(int dt in Directors)
                {
                    if(String.Compare(UserName, dt.ToString()) == 0)
                    {
                        userGroup = UserGroup.Director;
                        break;
                    }
                }

                //Check if he is the segment's manager
                int ManagerSN = GetSegmentManagerSN();
                if(String.Compare(ManagerSN.ToString(), UserName) == 0)
                {
                    userGroup = UserGroup.Manager;
                }
            }
            else 
            {
                //Check if the user is administrator
                cmd = new MySqlCommand("GetAdminGroup", m_conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                DataComponent.AddCmdParameters(cmd, "?adminPwd", MySqlDbType.VarChar, 45, Password);
                DataComponent.AddCmdParameters(cmd, "?adminSerialNum", MySqlDbType.Int32, 10, int.Parse(UserName));
                DataComponent.AddCmdOutParameters(cmd, "?adminGroup", MySqlDbType.Int32, 10);
                cmd.ExecuteNonQuery();

                if(cmd.Parameters["?adminGroup"].Value.GetType() != typeof(DBNull))
                {
                    userGroup = (UserGroup)cmd.Parameters["?adminGroup"].Value;
                }
            }
            return (int)userGroup;
        }
        catch(MySqlException err)
        {
            throw new ApplicationException("Data error.");
        }
        finally
        {
            m_conn.Close();
        }

    }
}
